import type { KnowledgeDetail } from '@/types/knowledge';

/**
 * 数据查询知识点数据
 */
export const queryKnowledge: KnowledgeDetail[] = [
	{
		id: 1,
		title: 'SELECT基础查询',
		description: 'SELECT语句是SQL中最常用的查询语句，用于从表中检索数据。',
		icon: '🔍',
		categoryId: 'sql-query',
		categoryName: '数据查询',
		categoryIcon: '🔍',
		difficulty: '入门',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #2ecc71 0%, #27ae60 100%)',
		keyPoints: [
			{
				point: '基本语法：SELECT column1, column2 FROM table_name。SELECT用于指定要查询的列，FROM指定数据来源表。',
				code: `-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, name, email FROM users;

-- 查询单个列
SELECT name FROM users;`
			},
			{
				point: '使用DISTINCT去重：去除查询结果中的重复行。',
				code: `-- 去重查询
SELECT DISTINCT city FROM users;
SELECT DISTINCT status, category FROM products;

-- 多列去重
SELECT DISTINCT user_id, product_id FROM orders;`
			},
			{
				point: '使用别名（AS）：为列或表指定别名，提高可读性。',
				code: `-- 列别名
SELECT 
    id AS user_id,
    name AS user_name,
    email AS user_email
FROM users;

-- 表别名（更短）
SELECT u.id, u.name, u.email
FROM users AS u;

-- AS关键字可以省略
SELECT u.id, u.name
FROM users u;`
			},
			{
				point: 'LIMIT和OFFSET：限制返回的行数和偏移量，用于分页查询。',
				code: `-- 限制返回行数
SELECT * FROM users LIMIT 10;

-- 分页查询（跳过前10条，取10条）
SELECT * FROM users LIMIT 10 OFFSET 10;

-- MySQL语法
SELECT * FROM users LIMIT 10, 10;  -- 等价于 LIMIT 10 OFFSET 10`
			},
			{
				point: '算术表达式：可以在SELECT中使用算术运算符和函数。',
				code: `-- 计算表达式
SELECT 
    name,
    price,
    quantity,
    price * quantity AS total_amount
FROM order_items;

-- 使用函数
SELECT 
    name,
    price,
    ROUND(price * 1.1, 2) AS price_with_tax
FROM products;`
			}
		]
	},
	{
		id: 2,
		title: 'WHERE条件筛选',
		description: 'WHERE子句用于过滤查询结果，只返回满足条件的行。',
		icon: '🔎',
		categoryId: 'sql-query',
		categoryName: '数据查询',
		categoryIcon: '🔍',
		difficulty: '入门',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #2ecc71 0%, #27ae60 100%)',
		keyPoints: [
			{
				point: '比较运算符：=、<>（不等于）、>、<、>=、<=用于数值和字符串比较。',
				code: `-- 等值查询
SELECT * FROM users WHERE age = 25;
SELECT * FROM products WHERE price = 99.99;

-- 不等查询
SELECT * FROM users WHERE age <> 25;
SELECT * FROM users WHERE age != 25;  -- 某些数据库支持

-- 范围查询
SELECT * FROM products WHERE price > 100;
SELECT * FROM users WHERE age >= 18 AND age <= 65;`
			},
			{
				point: 'LIKE模糊匹配：用于字符串模式匹配，%匹配任意字符，_匹配单个字符。',
				code: `-- 以某字符开头
SELECT * FROM users WHERE name LIKE '张%';

-- 以某字符结尾
SELECT * FROM products WHERE name LIKE '%手机%';

-- 单个字符匹配
SELECT * FROM users WHERE email LIKE 'user_@gmail.com';  -- 匹配 user1@, user2@ 等

-- 转义特殊字符
SELECT * FROM products WHERE name LIKE '%100%%' ESCAPE '\\';  -- 查找包含100%的`
			},
			{
				point: 'IN和NOT IN：判断值是否在指定列表中。',
				code: `-- IN查询
SELECT * FROM users WHERE city IN ('北京', '上海', '广州');
SELECT * FROM products WHERE category_id IN (1, 2, 3);

-- NOT IN查询
SELECT * FROM users WHERE city NOT IN ('北京', '上海');
SELECT * FROM products WHERE status NOT IN ('deleted', 'archived');`
			},
			{
				point: 'BETWEEN...AND：范围查询，包含边界值。',
				code: `-- 数值范围
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- 日期范围
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- NOT BETWEEN
SELECT * FROM users WHERE age NOT BETWEEN 18 AND 65;`
			},
			{
				point: 'IS NULL和IS NOT NULL：判断空值。NULL不能用=或<>比较。',
				code: `-- 查询空值
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM orders WHERE shipped_date IS NULL;

-- 查询非空值
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM products WHERE description IS NOT NULL;

-- ❌ 错误用法
-- SELECT * FROM users WHERE email = NULL;  -- 错误！
-- SELECT * FROM users WHERE email <> NULL;  -- 错误！
-- ✅ 正确用法
-- SELECT * FROM users WHERE email IS NULL;`
			}
		]
	},
	{
		id: 3,
		title: 'ORDER BY排序',
		description: 'ORDER BY用于对查询结果进行排序，可以按一个或多个列排序。',
		icon: '📊',
		categoryId: 'sql-query',
		categoryName: '数据查询',
		categoryIcon: '🔍',
		difficulty: '入门',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #2ecc71 0%, #27ae60 100%)',
		keyPoints: [
			{
				point: '基本排序：ORDER BY column_name ASC（升序，默认）或DESC（降序）。',
				code: `-- 升序排序（默认）
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age ASC;

-- 降序排序
SELECT * FROM products ORDER BY price DESC;
SELECT * FROM users ORDER BY created_at DESC;`
			},
			{
				point: '多列排序：可以按多个列排序，先按第一列，再按第二列。',
				code: `-- 多列排序
SELECT * FROM users 
ORDER BY age DESC, name ASC;

-- 先按年龄降序，年龄相同时按姓名升序
SELECT * FROM employees 
ORDER BY department_id ASC, salary DESC;`
			},
			{
				point: '按表达式排序：可以使用计算表达式或函数进行排序。',
				code: `-- 按计算结果排序
SELECT name, price, quantity, price * quantity AS total
FROM order_items
ORDER BY price * quantity DESC;

-- 按函数结果排序
SELECT name, UPPER(name) AS upper_name
FROM users
ORDER BY UPPER(name);`
			},
			{
				point: '按列位置排序：可以使用列在SELECT中的位置序号排序（不推荐）。',
				code: `-- 按列位置排序（第1列）
SELECT name, age, email FROM users ORDER BY 1;

-- 按第2列降序
SELECT name, age, email FROM users ORDER BY 2 DESC;

-- ⚠️ 不推荐：可读性差，列顺序改变会出错`
			},
			{
				point: 'NULL值排序：NULL值在排序时的处理方式，可以使用NULLS FIRST或NULLS LAST指定。',
				code: `-- NULL值默认排序（因数据库而异）
-- PostgreSQL: NULLS LAST（NULL值在最后）
SELECT * FROM users ORDER BY email DESC NULLS LAST;

-- PostgreSQL: NULLS FIRST（NULL值在最前）
SELECT * FROM users ORDER BY email ASC NULLS FIRST;

-- MySQL: NULL值总是排在最后（升序）或最前（降序）
SELECT * FROM users ORDER BY email ASC;  -- NULL在最后
SELECT * FROM users ORDER BY email DESC;  -- NULL在最前`
			}
		]
	}
];

